<?php
/* 
 * Aclass_sql
 * mysql 类
 */
class Aclass_mysql{

	function  __construct($data) {
		$this->dbname = $data['name'];
		$this->adbs = Afun_db_link($data);
		//$this->adbs->setattribute(PDO::ATTR_CASE,PDO::CASE_LOWER);
		//$this->adbs->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
		$this->cache_name = 'Adb:'.$this->dbname.':';
		$this->sql;
	}
//PDO
	function geterror(){
		$Acode = $this->adbs->errorCode();
		if($Acode == "00000"){
			return false;
		}else{
			$err = $this->adbs->errorInfo();
			$obj->code = $err[0];
			$obj->status = $err[1];
			$obj->msg = $this->errormsg($err);
			$obj->pdo = $err[2];
			$obj->sql = $this->sql;
			return $obj;
		}
	}
	function errormsg($arr){
		if($arr[1]=="1062"){
			preg_match_all("/'(.*?)'/i",$arr[2], $matches);
			$repeat = $matches[1][0];
			$field = $matches[1][1];
			if(!empty($this->comment[$field])){
				$field = $this->comment[$field];
			}
			$msg = "栏目 <span style=\"color:red;\">$field</span> 重复,已存在 <span style=\"color:red;\">$repeat</span>";
		}elseif($arr[1]=='1064'){
			$msg = "SQL语法错误:".$this->sql;
		}
		return $msg;
	}
	function exec($sql,$nolog=1){
		//print_r($sql);exit;
		//Afun_sql_save($sql);
		$this->sql = $sql;
		$qstart=gettime();
		$rs = $this->adbs->exec($sql);
		$times = round(gettime() - $qstart,6);
		if($nolog==1){
			mysql_query_times(1);
			mysql_query_sql($sql);
			if($times>1) Afunc_log()->mysql($sql,'exec',$times,Afun_uid(),$rs);
		}
		if(Acan_debug==1) Afun_debug_mysql($sql,$times);
		$error = $this->geterror();
		if($error){
			return $error;
		}
		return $rs;
	}
	function query($sql){
		//Afun_sql_save($sql);
		mysql_query_times(1);
		mysql_query_sql($sql);
		$qstart=gettime();
		$query = $this->adbs->query($sql) or die(Afunc_log()->write('SQL语句错误:'.$sql,'mysql'));
		$times = round(gettime() - $qstart,6);
		if($times>1) Afunc_log()->mysql($sql,'query',$times,Afun_uid());
		if(Acan_debug==1) Afun_debug_mysql($sql,$times);
		$this->geterror();
		return $query;
	}
	function check($str) {
		//$pattern = "/select|insert|update|delete|drop|alter|truncate|union|\%|\'|\"|\\\|char\(/i";
		$pattern = "/drop|alter|truncate|union|DUMPFILE|CONCAT\(|SLEEP\(|IFNULL\(|CAST\(|LENGTH\(|MID\(|IF\(|ORD\(|0x|\"|char\(/i";
		preg_match($pattern, $str, $matches);
		if(count($matches)>0){
			echo 'Aclass_mysql.php check ';
			print_r($matches);
			echo $str;
			echo '！警告！您的IP已经被系统记录、请不要输入非法地址！';
			exit;
		}else{
			return $str;
		}
	}
	function check_where($str) {
		$pattern = "/drop|alter|truncate|union|DUMPFILE|CONCAT\(|SLEEP\(|IFNULL\(|CAST\(|LENGTH\(|MID\(|IF\(|ORD\(|0x|char\(/i";
		preg_match($pattern, $str, $matches);
		if(count($matches)>0){
			echo 'Aclass_mysql.php check_where ';
			print_r($matches);
			echo $str;
			echo '！警告！您的IP已经被系统记录、请不要输入非法地址！';
			exit;
		}else{
			return $str;
		}
	}
	function lastInsertId(){
		return $this->adbs->lastInsertId();
	}
	function quote($str){
		return $this->adbs->quote($str);
	}
	function model($table="",$model=1){
		$this->select = 'a.*';
		if(!empty($table)) $this->table = $table;
		$this->columns($model);
		$this->key_name;
		return $this;
	}
	function ofind($val=''){
		return Afun_arr_obj($this->find($val));
	}
	function ofindPk($val){
		return Afun_arr_obj($this->findPk($val));
	}
	function ofindAll($val=''){
		return Afun_arr_obj($this->findAll($val));
	}
	function find($val=''){
		$sql = $this->unisql($val);
		if(stripos($sql,'LIMIT') === false){$sql .= " LIMIT 1";}
		return $this->query($sql)->fetch();
	}
	function findPk($pk){
		$sql = $this->unisql('`'.$this->key_name.'`=\''.$pk.'\'');
		if(stripos($sql,'LIMIT') === false){$sql .= " LIMIT 1";}
		return $this->query($sql)->fetch();
	}
	function findAll($val=''){
		$sql = $this->unisql($val);
		return $this->query($sql)->fetchAll();
	}
	function update($val,$con){
		$sql = $this->unisql_update($val,$con);
		return $this->exec($sql);
	}
	function updatePk($val,$pk){
		$sql = $this->unisql_update($val,'`'.$this->key_name.'`=\''.$pk.'\'');
		return $this->exec($sql);
	}
	function updateAll($val,$con=''){
		$sql = $this->unisql_update($val,$con);
		return $this->exec($sql);
	}
	function save($val,$type=0){
		if(is_array($val)){
			if(empty($val[$this->key_name]) || $val[$this->key_name] == ''){
				$sql = $this->unisql_insert($val);
			}else if($type==1){
				$sql = $this->unisql_insert($val);
			}else{
				if($type==2){
					if(!$this->findPk($val[$this->key_name])){
						$rs = $this->exec($this->unisql_insert($val));
					}
				}
				$rs = $this->updatePk($val,$val[$this->key_name]);
			}
		}elseif(is_object($val)){
			$key_name = $this->key_name;
			if(empty($val->$key_name) || $val->$key_name == ''){
				$sql = $this->unisql_insert($val);
			}else if($type==1){
				$sql = $this->unisql_insert($val);
			}else{
				$rs = $this->updatePk($val,$val->$key_name);
			}
		}
		if(isset($sql)){
			$rs = $this->exec($sql);
		}
		return $rs;
	}
	function count($val=''){
		$sql = $this->unisql($val);
		$sql = preg_replace("/SELECT([0-9a-zA-Z.*_-\s`,]+)FROM/i",'SELECT count(*) FROM',$sql);
		$data = $this->query($sql)->fetch();
		return $data[0];
	}
	function delete($val){
		$sql = $this->unisql_del($val);
		return $this->exec($sql);
	}
	function deletePk($pk){
		$sql = $this->unisql_del('`'.$this->key_name.'`=\''.$pk.'\'');
		return $this->exec($sql);
	}
	//点击计数
	function click($field,$con){
		return  $this->update("$field = $field+1",$con);
	}
	function clickPk($field,$id){
		return  $this->updatePk("$field = $field+1",$id);
	}
	function unisql_del($val,$data=''){
		if(is_string($val)){
			if(is_array($data)){
				$where = "WHERE '$val' in (".$this->inall($data).")";
			}else{
				$where = "WHERE ".$val;
			}
		}elseif(is_array($val) || is_object($val)){
			foreach($val as $k=>$v){
				if(empty($v) && $v != 0){continue;}
				if(!in_array($k,$this->fields)){continue;}
				$v = str_replace("'","\\'",str_replace("\\'","'",$v));
				if(empty($where)){
					$where = "WHERE `$k` = '$v' ";
				}else{
					$where .= "and `$k` = '$v' ";
				}
			}
		}
		return "DELETE FROM $this->table ".$where;
	}
	function unisql_insert($val){
		foreach($val as $k=>$v){
			if(empty($v) && $v != 0){continue;}
			if(!in_array($k,$this->fields)){continue;}
			$v = str_replace("'","\\'",str_replace("\\'","'",$v));
			if(empty($col)){
				$col = '`'.$k.'`';
				$value = '\''.$v.'\'';
			}else{
				$col .= ',`'.$k.'`';
				$value .= ',\''.$v.'\'';
			}
		}
		return "INSERT INTO $this->table ($col) VALUES($value)";
	}
	function unisql_update($val,$con){
		if(is_array($val) || is_object($val)){
			foreach($val as $k=>$v){
				if(empty($v) && $v != 0){continue;}
				if(!in_array($k,$this->fields)){continue;}
				$v = str_replace("'","\\'",str_replace("\\'","'",$v));
				if(empty($set)){
					$set = ' SET `'.$k.'`=\''.$v.'\'';
				}else{
					$set .= ',`'.$k.'`=\''.$v.'\'';
				}
			}
		}elseif(is_string($val)){
			$set = ' SET '.$val;
		}
		if(is_string($con)){//字符串条件
			$where = ' WHERE '.$con;
		}elseif(is_array($con)){//数组条件
			foreach($con as $k=>$v){
				if(empty($v) && $v != 0){continue;}
				if(!in_array($k,$this->fields)){continue;}
				$v = str_replace("'","\\'",str_replace("\\'","'",$v));
				if(empty($where)){
					$where = ' WHERE `'.$k.'` = \''.$v.'\'';
				}else{
					$where .= ' and `'.$k.'` = \''.$v.'\'';
				}
			}
		}elseif(is_object($con)){//对象条件
			if($con->select){	$this->select = $con->select;}
			if($con->limit)
				$limit = ' LIMIT '.$con->limit;
			if($con->where){
				$this->where = ' WHERE '.$con->where;
			}
			if($con->join)
				$join = $con->join;
			if($con->group){
				$group = ' GROUP BY '.$con->group;
			}
			if($con->order){
				$by .= ' ORDER BY '.$con->order;
			}
			$where = $join.$this->where.$group.$by.$limit;
		}
		return "UPDATE $this->table".$set.$where;
	}
	
	function unisql($val){
		$this->model;
		if(is_string($val)){//字符串条件
			$where = ' WHERE '.$val;
			if(empty($val)){$where = '';}
		}
		if(is_array($val)){//数组条件
			foreach($val as $k=>$v){
				if(empty($v) && $v != 0){continue;}
				if(!in_array($k,$this->fields)){continue;}
				if(empty($where)){
					$where = ' WHERE `'.$k.'` = \''.$this->check($v).'\'';
				}else{
					$where .= ' and `'.$k.'` = \''.$this->check($v).'\'';
				}
			}
		}
		if(is_object($val)){//对象条件
			if($val->select){	$this->select = $val->select;}
			if($val->limit)
				$limit = ' LIMIT '.$val->limit;
			if($val->where){
				$this->where = ' WHERE '.$val->where;
			}
			if($val->join)
				$join = $val->join;
			if($val->group){
				$group = ' GROUP BY '.$val->group;
			}
			if($val->order){
				$by .= ' ORDER BY '.$val->order;
			}
			$where = $join.$this->where.$group.$by.$limit;
		}
		return 'SELECT '.$this->select.' FROM '.$this->table.' a '.$this->check_where($where);
	}
	/*	取得当前表的key  返回表的栏目属性  并缓存1小时	*/
	function columns($model=1){
		$cache_name = $this->cache_name.$this->table;
		$data = Afun_cache_get($cache_name);
		$modelfile = A_ROOT."/model/".$this->table.".php";
		if(file_exists($modelfile) && $model == 1){include(A_ROOT."/model/".$this->table.".php");}
		if(empty($data)){
			$sql = "SHOW FULL FIELDS FROM $this->table";
			$datas = $this->query($sql)->fetchAll();
			foreach($datas as $k=>$val){
				if($val['Key'] == 'PRI' && empty($data['key'])){
					$data['key'] = $val['Field'];
				}
				$fields = $val['Field'];
				$data['fields'][$k] = $fields;
				$trr = explode("(",$val['Type']);
				$data['types'][$fields] = $trr[0];
				if(count($trr)==2){
					$data['length'][$fields] = substr($trr[1],0,-1);
				}
				$data['null'][$fields] = $val['Null'];
				$data['default'][$fields] = $val['Default'];
				$data['extra'][$fields] = $val['Extra'];
				$crr = explode(',',$val['Comment']);
				if($crr[1]){$data['enum'][$fields] = $crr[1];}
				$data['comment'][$fields] = $crr[0];
				//$data['form'][$fields] = $forms[$fields];
			/*	$data['type'][$fields] = $val['DATA_TYPE'];
				if($val['DATA_TYPE']=='int' || $val['DATA_TYPE']=='tinyint'){
					$data['length'][$fields] = $val['NUMERIC_PRECISION'];
				}else{
					$data['length'][$fields] = $val['CHARACTER_MAXIMUM_LENGTH'];
				}*/
			}
			if($model == 1){
				Afun_cache_set($cache_name,$data,3600);
			}
		}
		//--栏目表单
		if(is_object($form) || is_array($form)){
			foreach ($form as $k => $v) {
				if(isset($v)){
					$forms[$k] = json_decode($v);
				}else{
					if($k==$data['key']){
						$forms[$k]->type = 'key';
					}elseif(in_array($data['types'][$k],array('int','tinyint','smallint','mediumint','mediumint','integer','bigint'))){
						$forms[$k]->type = 'number';
						$forms[$k]->size = $data['length'][$k];
					}elseif($data['types'][$k]=='datetime'){
						$forms[$k]->type = 'datetime';
					}elseif($data['types'][$k]=='date'){
						$forms[$k]->type = 'date';
					}elseif($data['types'][$k]=='text'){
						$forms[$k]->type = 'textarea';
						$forms[$k]->style = "height:50px;";
					}else{
						$forms[$k]->type = 'text';
						$forms[$k]->size = $data['length'][$k];
					}
				}
				$forms[$k]->name = $k;
				if($forms[$k]->type=='none'){unset($data['comment'][$k],$forms[$k]);}//排除不需要显示的
			}
			$forms[$data['key']]->type = 'key';
		}
		$data['form'] = $forms;
		//--栏目枚举
		if(is_object($enum) || is_array($enum)){
			foreach ($enum as $k => $v) {
				$data['enum'][$k] = $v;
			}
		}
		//--栏目验证
		if(is_object($check) || is_array($check)){
			foreach ($check as $k => $v) {
				$data['check'][$k] = $v;
			}
		}
		//--栏目正则
		if(is_object($regexp) || is_array($regexp)){
			foreach ($regexp as $k => $v) {
				$data['regexp'][$k] = $v;
			}
		}
		$this->key_name = $data['key'];
		$this->fields = $data['fields'];
		$this->comment = $data['comment'];
		$this->form = $data['form'];
		$this->enum = $data['enum'];
		$this->check = $data['check'];
		$this->regexp = $data['regexp'];
		$this->columns = $data;
		return $this;
	}
	
	function inall($arr){
		foreach($arr as $value){
			if(empty($allid))
				$allid = "'".(int)$value."'";
			else
				$allid.= ",'".(int)$value."'";
		}
		return $allid;
	}
	
	function adbs($host='',$user='',$pass='',$name='',$port='',$charset=''){
		if(!$host){	$host = Acan_db_host;}
		if(!$port){	$port = Acan_db_port;}
		if(!$user){	$user = Acan_db_user;}
		if(!$pass){	$pass = Acan_db_pass;}
		if(!$name){	$name = Acan_db_name;}
		if(!$charset){	$charset = Acan_db_charset;}
		return Afun_db_link($host,$user,$pass,$name,$charset);
	}
	//Asql update(数据,表名,条件)
	function update2($arr,$table,$where){
		foreach($arr as $key=>$val){
			if(empty($sql_save[$i])){
				$sql_save = "`$key`='".$val."'";
			}else{
				$sql_save .= ",`$key`='".$val."'";
			}
		}
		$sql = "UPDATE ".Acan_db_pre.$table." SET ".$sql_save." WHERE ".$where;
		if($this->exec($sql))
			return true;
		else
			return false;
	}
	//Asql create(数据,表名,条件)
	function create($arr,$table=''){
		if($table=='') $table = $this->table;
		else $table = Acan_db_pre.$table;
		foreach($arr as $col=>$val){
			if(empty($cola)){
				$cola = '`'.$col.'`';
				$colb = "'".$val."'";
			}else{
				$cola .= ',`'.$col.'`';
				$colb .= ",'".$val."'";
			}
		}
		$cgstr="INSERT INTO ".$table." ($cola) VALUES ($colb)";
		if($this->exec($cgstr))
			return true;
		else
			return false;
	}

	//Asql del(数据,表名,条件)
	function Asql_delKey($key,$table){
		$sql = "DELETE FROM ".Acan_db_pre.$table." WHERE id = '".$age['del_id']."'";
		if($this->exec($sql))
			return true;
		else
			return false;
	}
	//删除缓存
	function cache_del($table=""){
		if(!empty($table)) $this->table = $table;
		return Afun_cache_rm($this->cache_name.$this->table);
	}


}
?>
